package pandora.data.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pandora.connection.ConnectionManager;
import pandora.data.ICustomerDAO;
import pandora.data.IGenericSearchDAO;
import pandora.data.IProductDAO;
import pandora.data.IStoreDAO;
import pandora.data.ITypeDAO;
import pandora.dto.CustomerDTO;
import pandora.dto.ProductDTO;
import pandora.dto.TypeDTO;
import pandora.utils.UIDGenerator;


public class JDBCGenericSearchDAO implements IGenericSearchDAO {

	private Connection conn;
	private IStoreDAO store;
	private ICustomerDAO customer;
	private ITypeDAO type;
	
	public JDBCGenericSearchDAO(){
		conn = ConnectionManager.getInstance().checkOut();
		store = new JDBCStoreDAO();
		customer = new JDBCCustomerDAO();
		type = new JDBCTypeDAO();
	}
	
    protected void finalize() {
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	//Métodos DCC
	public Integer countSearchedCustomerType(String cdni, TypeDTO t) {
		Integer counterC = 0;
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		String typeOID = null;
		
		String sql = "SELECT count(*) FROM Types T,GenericSearch G "+
						"WHERE T.OID = G.typeOID "+
						"AND G.customerOID = ? "+
						"AND T.OID = ? ;";

		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			typeOID = type.selectTypeOIDByTypeName(t);
			
			if(customerOID != null && typeOID != null){
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,customerOID);
				stmt.setString(2,typeOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				counterC = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return counterC;
	}

	public Integer countSearchedCustomerTypeBetween(String cdni, TypeDTO t,
			Date d1, Date d2) {
		Integer counterCB = 0;
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		String typeOID = null;
		
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT count(*) FROM Types T,GenericSearch G "+
						"WHERE T.OID = G.typeOID "+
						"AND G.date BETWEEN ? AND ? "+
						"AND G.customerOID = ? "+
						"AND T.OID = ? ;";

		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			typeOID = type.selectTypeOIDByTypeName(t);
			
			if(customerOID != null && typeOID != null){
				stmt = conn.prepareStatement(sql);
				
				time1 = d1.getTime();
		        time2 = d2.getTime();
		        
		        stmt.setString(1,time1.toString());
		        stmt.setString(2,time2.toString());
				stmt.setString(3,customerOID);
				stmt.setString(4,typeOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				counterCB = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return counterCB;
	}

	public Integer countSearchedTypeAtStore(String snif, TypeDTO t) {
		Integer counterS = 0;
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		String typeOID = null;
		
		String sql = "SELECT count(*) FROM Prices R,Products P,Types T,GenericSearch G "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND P.typeOID = T.OID "+
						"AND T.OID = G.typeOID "+
						"AND R.storeOID = ? "+
						"AND T.OID = ? ;";
		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			typeOID = type.selectTypeOIDByTypeName(t);
			
			if(storeOID != null && typeOID != null){
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,storeOID);
				stmt.setString(2,typeOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				counterS = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return counterS;
	}

	public Integer countSearchedTypeAtStoreBetween(String snif, TypeDTO t,
			Date d1, Date d2) {
		Integer counterSB = 0;
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		String typeOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT count(*) FROM Prices R,Products P,Types T,GenericSearch G "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND G.date BETWEEN ? AND ? "+
						"AND P.typeOID = T.OID "+
						"AND T.OID = G.typeOID "+
						"AND R.storeOID = ? "+
						"AND T.OID = ? ;";
		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			typeOID = type.selectTypeOIDByTypeName(t);
			
			if(storeOID != null && typeOID != null){
				stmt = conn.prepareStatement(sql);
				
				time1 = d1.getTime();
				time2 = d2.getTime();
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,storeOID);
				stmt.setString(4,typeOID);
				
				result = stmt.executeQuery();
			
				result.next();
				
				counterSB = result.getInt("count(*)");

			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return counterSB;
	}

	public List<TypeDTO> selectAllSearchedCustomerTypes(String cdni) {
		List<TypeDTO> sct = new ArrayList<TypeDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		
		String sql = "SELECT DISTINCT T.* FROM Types T,GenericSearch G "+
						"WHERE T.OID = G.typeOID "+
						"AND G.customerOID = ? ;";
		
		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			
			if(customerOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,customerOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    TypeDTO t = new TypeDTO();
                    
                    t.setParentType(type.selectTypeByOID(result.getString("parentOID")));
                    t.setType(result.getString("type"));
                                       
                    sct.add(t);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return sct;
	}

	public List<TypeDTO> selectAllSearchedCustomerTypesBetween(String cdni,
			Date d1, Date d2) {
		List<TypeDTO> sctb = new ArrayList<TypeDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String customerOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long (0);

		String sql = "SELECT DISTINCT T.* FROM Types T,GenericSearch G "+
						"WHERE T.OID = G.typeOID "+
						"AND G.date BETWEEN ? AND ? "+
						"AND G.customerOID = ? ;";
		
		try{
			customerOID = customer.selectCustomerOIDByDNI(cdni);
			
			if(customerOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				time1 = d1.getTime();
		        time2 = d2.getTime();
		        
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,customerOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    TypeDTO t = new TypeDTO();
                    
                    t.setParentType(type.selectTypeByOID(result.getString("parentOID")));
                    t.setType(result.getString("type"));
                                       
                    sctb.add(t);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return sctb;
	}

	public List<TypeDTO> selectAllSearchedTypesAtStore(String snif) {
		List<TypeDTO> sts = new ArrayList<TypeDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		
		String sql = "SELECT DISTINCT T.* FROM Prices R,Products P,Types T,GenericSearch G "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND P.typeOID = T.OID "+
						"AND T.OID = G.typeOID "+
						"AND R.storeOID = ? ;";
		
		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			
			if(storeOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				stmt.setString(1,storeOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    TypeDTO t = new TypeDTO();
                    
                    t.setParentType(type.selectTypeByOID(result.getString("parentOID")));
                    t.setType(result.getString("type"));
                                       
                    sts.add(t);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return sts;
	}

	public List<TypeDTO> selectAllSearchedTypesAtStoreBetween(String snif,
			Date d1, Date d2) {
		List<TypeDTO> stsb = new ArrayList<TypeDTO>();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String storeOID = null;
		Long time1 = new Long(0);
		Long time2 = new Long(0);
		
		String sql = "SELECT DISTINCT T.* FROM Prices R,Products P,Types T,GenericSearch G "+
						"WHERE R.productOID = P.OID "+
						"AND R.valid = 1 "+
						"AND G.date BETWEEN ? AND ? "+
						"AND P.typeOID = T.OID "+
						"AND T.OID = G.typeOID "+
						"AND R.storeOID = ? ;";
		
		try{
			storeOID = store.selectStoreOIDByNIF(snif);
			
			if(storeOID != null){
                
				stmt = conn.prepareStatement(sql);
				
				time1 = d1.getTime();
				time2 = d2.getTime();
				
				stmt.setString(1,time1.toString());
				stmt.setString(2,time2.toString());
				stmt.setString(3,storeOID);
				
				result = stmt.executeQuery();
			
				while(result.next()){
                    TypeDTO t = new TypeDTO();
                    
                    t.setParentType(type.selectTypeByOID(result.getString("parentOID")));
                    t.setType(result.getString("type"));
                                       
                    stsb.add(t);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
            try {
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return stsb;
	}
	//Fin Métodos DCC
	
	//AVC
	
public void saveSearch(ProductDTO product, String user, Date date) {
		
		String OID = UIDGenerator.getInstance().getKey();
		
		IProductDAO productDAO = new JDBCProductDAO();
		
		String producto= productDAO.selectOIDbyName(product.getProductName());
		
		ICustomerDAO customerDAO = new JDBCCustomerDAO();
		
		CustomerDTO customer= customerDAO.selectCustomerByAccount(user);
		String usuarioOID = customerDAO.selectCustomerOIDByDNI(customer.getCustomerDNI());
		
		Long today = System.currentTimeMillis();
		
		System.out.println(OID);
		System.out.println(producto);

		System.out.println(usuarioOID);
		System.out.println(today);
		
		String sql = "insert into Searchs (OID, productOID,customerOID,date) values ('"+OID+"','"+producto+"','"+usuarioOID+"',"+today+")";
		System.out.println(sql);		
				
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
		    System.out.println("Message: " + e.getMessage());
		    System.out.println("SQLState: " + e.getSQLState());
		    System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
		    try {
		    	if (stmt != null)
		            stmt.close();
		        } catch (SQLException e) {
		        	System.out.println("RESULTSET CLOSING ERROR OR STATEMENT CLOSING ERROR");
		        }
		    }
		}


}
